echo off
mkdir logs
set logfile=logs\%2.dep_%db2_server_name%.out
date /t >>%logfile%
time /t >>%logfile%

echo ---SPs which depend on  %2 --- >> %logfile%
db2 "select substr(proc.procschema,1,10) as proschema, proc.procname from syscat.routines r, syscat.procedures proc, syscat.packagedep pdep where pdep.bname=upper('%2') and pdep.bschema=upper('%1') and r.specificname=proc.specificname AND pdep.pkgname = 'P'||SUBSTR(CHAR(r.lib_id+10000000),2)"  >> %logfile%

echo --- UDFs which depend on  %2--- >> %logfile%
db2 select substr(funcschema,1,10) as funschema, funcname from syscat.funcdep where bschema = upper('%1')  and bname = upper('%2')   order by bname >> %logfile%

echo --- view which depend on %2--- >> %logfile%
db2 select substr(viewschema,1,10) as viewschema, viewname from syscat.viewdep where bschema = upper('%1')  and bname = upper('%2')  order by bname >> %logfile%

echo --- UDF which depend on  %2--- >> %logfile%
db2 "select substr(dschema,1,10) as dschema, dname from sysibm.sysdependencies where dtype='F' and bname in ( select SPECIFICNAME from SYSCAT.functions where funcname=upper('%2') and FUNCSCHEMA=upper('%1'))" >> %logfile%

echo ---tables which refrence table %2 --- >> %logfile%
db2 select * from   SYSCAT.REFERENCES where REFTABNAME =upper('%2') >> %logfile%
start %logfile%
